<?php
    include 'base.php';
    include 'requireLogin.php';
    include 'connectdb.php';        
    include 'validate.php';        
    require 'Classes/PHPExcel.php';


    function get_price($product_code){         
        $sql1 = "SELECT sale_price from product WHERE code = '".$product_code."'";        
        $result = mysql_query( $sql1 ) OR DIE( "My query ($query) generated an error: ".mysql_error() );  
        $prices = mysql_fetch_array($result);
        return $prices['sale_price'];                     
    }

    $path = $_GET['file'];
    require_once 'Classes/PHPExcel/IOFactory.php';
    $objPHPExcel = PHPExcel_IOFactory::load($path);    
    $success = 0;    
    $fail = 0;    
    foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
        $worksheetTitle     = $worksheet->getTitle();
        $highestRow         = $worksheet->getHighestRow(); // e.g. 10
        $highestColumn      = $worksheet->getHighestColumn(); // e.g 'F'
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);                        

        echo "<table class=\"table\" style=\"table-layout:fixed; \">";        
        for ($row = 1; $row <= $highestRow;  $row++) {
            $val=array();            
            for ($col = 0; $col < $highestColumnIndex; $col++) {                
                $cell = $worksheet->getCellByColumnAndRow($col, $row);                
                if ($col == 5)
                    $val[]= PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), "YYYY-M-D");
                else
                    $val[] = $cell->getValue();                            
            }
            if ($row == 1){
                echo "<tr>";
                for ($col = 0; $col < $highestColumnIndex; $col++)
                    echo "<th>".$val[$col]."</th>";                
                echo "<th>Giá tiền</th>";
                echo "<th>Tổng</th>";                
                echo "<th>Trạng thái</th>";
                echo "</tr>";
            }
            else{
                echo "<tr>";
                $val[] = get_price($val[0]);
                $val[] = $val[6]*$val[1]*(100-$val[5])/100;
                for ($col = 0; $col < $highestColumnIndex+2; $col++)
                    echo "<td>".$val[$col]."</td>";
               
                if (is_product($val[0]) && is_seller($val[3]) && is_customer($val[2])){
                    $sql = "INSERT INTO bill(product_code, quantity, customer_code, seller_code, discount, date, price, total) 
                    VALUES ('".$val[0]."', '".$val[1]."', '".$val[2]."','".$val[3]."', '".$val[4]."','".$val[5]."', '".$val[6]."', '".$val[7]."')";                
                    if (mysql_query($sql, $co)){
                        echo "<td>Thành công</td></tr>";
                        $success++;
                    }
                    else {
                        echo "<td>Lỗi</td></tr>";
                        $fail++;
                    }
                }
                else  {
                        echo "<td>Lỗi</td></tr>";
                        $fail++;
                    }
            }
        }        
        break;
    }
    echo "</table>";
    echo "<ul>";
    echo "<li>Tổng <b>".($success + $fail)." </b></li>";
    echo "<li>Thành công  <b>".$success. " </b></li>";
    echo "<li>Lỗi  <b>".$fail. "</b></li></ul>";
    include 'footer.php';
    ?>    